USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[ResultEkidenKukanList]    スクリプト日付: 11/19/2024 09:44:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultEkidenKukanList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultEkidenKukanList]
GO

CREATE PROCEDURE [dbo].[ResultEkidenKukanList]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
  @ROUNDNO int,
	@KUMINO int,
  @SOUJUN int 
AS
BEGIN

SELECT ORD.TAIKAINO
    ,  ORD.KYOGINO
    ,  ORD.SUBNO
    ,  ORD.LANE
    ,  LAP.SYUMOKUNO
    ,  ORD.SOUJUN
    ,  KYO.KYOGISYANO
    ,  KYO.KYOGISYAMEI
    ,  KYO.KYOGISYAKANA
    ,  KYO.KOJINSYOZOKUTIMEI
    ,  KYO.GAKUNEN
    ,  KYO.SEINEN
    ,  SYO.SYOZOKUMEI
    ,  STI.SYOZOKUTIMEI
	  ,  KYOT.KYOGISYANO AS TEAMNO
    ,  KYOT.KYOGISYAMEI AS TEAMMEI
    ,  KYOT.KYOGISYAKANA AS TEAMKANA
    ,  COM.COMMENTRYAKU
    ,  COM.BUNRUI
    , CASE RSI.EXTEND3 WHEN '' THEN '9999' ELSE CAST(REPLACE(ISNULL(RSI.EXTEND3,9999),'^0',9999) AS INT) END AS EXTEND3
    , CASE RSI.EXTEND4 WHEN '' THEN '9999' ELSE CAST(REPLACE(ISNULL(RSI.EXTEND4,9999),'^0',9999) AS INT) END AS EXTEND4
    ,  SUBSTRING(KUM.KAISI,1,2) + ':' + SUBSTRING(KUM.KAISI,3,4)AS KAISI
    ,  KUM.ROLLCALLFLG
    ,  KUM.KYOGIFLG
    ,  RLA.KYOGIFLG2
	  ,  LAN.SYUKKETU
FROM TBL_ORDER AS ORD

LEFT JOIN TBL_KYOGISYA AS KYO
  ON ORD.TAIKAINO = KYO.TAIKAINO
  AND ORD.KYOGISYANO = KYO.KYOGISYANO

LEFT JOIN TBL_SYOZOKU AS SYO
  ON KYO.TAIKAINO = SYO.TAIKAINO
  AND KYO.SYOZOKUNO1 = SYO.SYOZOKUNO

LEFT JOIN TBL_SYOZOKUTI AS STI
  ON SYO.SYOZOKUTINO = STI.SYOZOKUTINO

LEFT JOIN TBL_TEAM AS TEA
  ON ORD.TAIKAINO = TEA.TAIKAINO
  AND KYO.KYOGISYANO = TEA.KYOGISYANO

LEFT JOIN TBL_KYOGISYA AS KYOT
  ON ORD.TAIKAINO = KYOT.TAIKAINO
  AND TEA.TEAMNO = KYOT.KYOGISYANO

LEFT JOIN TBL_RESULT_SIGI AS RSI
  ON ORD.TAIKAINO = RSI.TAIKAINO
  AND ORD.KYOGINO = RSI.KYOGINO
  AND ORD.SUBNO = RSI.SUBNO
  AND ORD.ROUNDNO = RSI.ROUNDNO
  AND ORD.KUMINO = RSI.KUMINO
  AND ORD.LANE = RSI.LANE 
  AND RSI.SIGINO = (ORD.SOUJUN*2)

LEFT JOIN TBL_KUMI AS KUM
  ON ORD.TAIKAINO = KUM.TAIKAINO
  AND ORD.KYOGINO = KUM.KYOGINO
  AND ORD.SUBNO = KUM.SUBNO
  AND ORD.ROUNDNO = KUM.ROUNDNO
  AND ORD.KUMINO = KUM.KUMINO

LEFT JOIN TBL_KYOGI AS KYOG
  ON ORD.TAIKAINO = KYOG.TAIKAINO
  AND ORD.KYOGINO = KYOG.KYOGINO

LEFT JOIN TBL_LAP AS LAP
  ON KYOG.SYUMOKUNO = LAP.SYUMOKUNO
  AND ORD.SOUJUN = LAP.ID

LEFT JOIN TBL_RESULT_LAP AS RLA
  ON ORD.TAIKAINO = RLA.TAIKAINO
  AND ORD.KYOGINO = RLA.KYOGINO
  AND ORD.SUBNO = RLA.SUBNO
  AND ORD.ROUNDNO = RLA.ROUNDNO
  AND ORD.KUMINO = RLA.KUMINO
  AND LAP.LAPNO = RLA.LAPNO

LEFT JOIN TBL_COMMENT AS COM
  ON RSI.EXTEND1 = COM.COMMENTNO

LEFT JOIN TBL_LANE AS LAN
  ON ORD.TAIKAINO = LAN.TAIKAINO
  AND ORD.KYOGINO = LAN.KYOGINO
  AND ORD.SUBNO = LAN.SUBNO
  AND ORD.ROUNDNO = LAN.ROUNDNO
  AND ORD.KUMINO = LAN.KUMINO
  AND ORD.LANE = LAN.LANE

WHERE ORD.TAIKAINO = @TAIKAINO
   AND ORD.KYOGINO = @KYOGINO
   AND ORD.SUBNO = @SUBNO
   AND ORD.ROUNDNO = @ROUNDNO
   AND ORD.KUMINO = @KUMINO
   AND ORD.SOUJUN = @SOUJUN
   

  ORDER BY KYOT.KYOGISYANO


END

